CODEWITHSUNDEEP
powerbidax
rubhemaju
rubhemaju

Reputation: 39

power bi dax, convert column to measure sliced by id column / column to sliceable measure

Bottom line: How do you define measures that respond to slicers in powerbi?

I have a list of customers that subscribe to a fictional phone plan. I have one table that list the customers and how many minutes they bought and another table that lists their daily consumption. Sample data:

customer_info:

id      minutes_bought
1       10
2       20
-----------------------------

daily_consumption:

customer id      date            minutes_used
1               2022-01-01      1
1               2022-01-02      2

Minutes are recorded cumulatively so if the customer has used 1 yesterday and 2 today, the record for today shows 3

customer_info[id] and daily_consumption[customer_id] are linked with 1 to many relationship

I have defined date and customer ID slicers and I would like to create a gauge that shows the customer's consumption compared to what they bought. The gauge must respond to the slicers.

for minutes used I created a measure

minutes used = CALCULATE(SUM('daily_consumption'[minutes_used]), LASTDATE('daily_consumption'[date]))

This calculates for every user but will show only 1 user if i slice it

I then tried to make a measure for the minutes bought

minutes bought = SUM('customer_info'[minutes_bought])

But this measure isnt responsive at all. It just returns the sum for all rows. It seems like slicing requires some kind of time filter in the DAX because I havent managed to slice any measures without in my report.

For my situation, how do I make the minutes_used measure respond to slicers. More generally how do you define measures that respond to slicers? I only know that aggregate functions are not the answer

Upvotes: 0

Views: 368

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

I assume that the unreactive slicers you refer to come from the daily_consumption table. Unless you set the cross-filter direction for the relationship between the daily_consumption and customer_info tables to bi-directional, filtering will not propagate from the daily_consumption table to the customer_info table, since the former is on the 'many' side of the one-to-many relationship with the latter, and, in a mono-directional relationship, filtering only ever propagates from the one side to the many side, not vice versa.

Upvotes: 0

Related Questions