Slicer selection on New Table (DAX)? if not, what are the alternatives?

[Anonymous]

Hello,

I have created a new table with below formula

Rate =

VAR _max = MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])

VAR _min = MinX(ALLSELECTED('Calendar'),'Calendar'[Date])

return

DISTINCT(UNION(

SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[COMPLETED] >=_min && 'EVENTS'[COMPLETED] <=_max), 'EVENTS'[CODE]),

SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[RSTATUS] = "R" && 'EVENTS'[SCHEDEND] <=_min), 'EVENTS'[CODE]),

SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[SCHEDEND] >=_min && 'EVENTS'[SCHEDEND] <=_max && 'EVENTS'[COMPLETED] >_max), 'EVENTS'[CODE])))

The table is created successfully, however, I would like the data to slice based on calendar selection. As you can see in the below image, the Calendar is connected to EVENTS[COMPLETED] & EVENTS[SCHEDEND] fields with dotted line connection. 

I have two questions:

1. is it possible to influence the table created with above DAX formula by user slicer? If a user selects Mar-2021, then the date filter should apply on the DAX table. If yes, how?

2. if it's not possible to influence the DAX table with user slicer, how else can i create a similar formula to make it work. It has to consider 3 different conditions as mentioned in the formula - 

Take the list of [CODE] based on 3 different conditions and finally show the distinct count of [CODE] from these 3 lists put together. A [CODE] can satisfy more than 1 condition (A [CODE] can satisfy Condition 1 & 2, but should be counted once), hence it's required to take the disctinct count of [CODE] combing the 3 conditions. How can i achieve this?

Any help on this is much appreciated. Thank you.

[amitchandak]

@Anonymous , a new table can not use slicer values , you can only use slicer value in a measure and var table inside a measure.

You can vore for an idea or log a new one

https://ideas.powerbi.com/ideas/

[Anonymous]

Hi @amitchandak 

Thank you for the response.

So you mean, i have to create a measure instead of a New Table with that formula? and use the selections inside the Var Table within that measure?

If yes, then how can i modify the below formula to fit into a measure to calculate the distinct count of [CODE]? 

Rate =

VAR _max = MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])

VAR _min = MinX(ALLSELECTED('Calendar'),'Calendar'[Date])

return

DISTINCT(UNION(

SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[COMPLETED] >=_min && 'EVENTS'[COMPLETED] <=_max), 'EVENTS'[CODE]),

SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[RSTATUS] = "R" && 'EVENTS'[SCHEDEND] <=_min), 'EVENTS'[CODE]),

SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[SCHEDEND] >=_min && 'EVENTS'[SCHEDEND] <=_max && 'EVENTS'[COMPLETED] >_max), 'EVENTS'[CODE])))

[Anonymous]

Hi @Anonymous ,

You can not create new table based on slicer value because it will not recognize. So the max and min value will also be the max and min of the entire table.

If you want to calculate the count of value based on the a slicer, you could refer the below formula.

measure = 
VAR _max = MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])
VAR _min = MinX(ALLSELECTED('Calendar'),'Calendar'[Date])
return
calculate(distinctcount('table'[code]),filter(all('table'),column1>=_min&&column2<=max))

Best Regards,

Jay

[Anonymous]

Hi @Anonymous ,

Thank you for the response. But I would like to get the count of distinct values from 3 different conditions as mentioned in my post.

Cond 1: SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[COMPLETED] >=_min && 'EVENTS'[COMPLETED] <=_max), 'EVENTS'[CODE])

Cond 2: SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[RSTATUS] = "R" && 'EVENTS'[SCHEDEND] <=_min), 'EVENTS'[CODE])

Cond 3: SUMMARIZE(FILTER(ALL('EVENTS'), NOT 'EVENTS'[STATUS] IN {"CANCEL", "REJECTED"} && 'EVENTS'[SCHEDEND] >=_min && 'EVENTS'[SCHEDEND] <=_max && 'EVENTS'[COMPLETED] >_max), 'EVENTS'[CODE])))

I will get a list of 'EVENTS'[CODE] for each of these conditions. When the 3 lists are put together, I need to get a distinct count of all 'EVENTS'[CODE]. How can I get this?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try to define a temporary table in the measure.

measure = 
VAR _max = MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])
VAR _min = MinX(ALLSELECTED('Calendar'),'Calendar'[Date])
var cond1 = ...
var cond2 = ...
var cond3 = ...
var _union = uinon(cond1,cond2,cond3)
return
CALCULATE(DISTINCTCOUNT([date]),_union)

Best Regards,

Jay