Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Im trying to create a line chart using below scenario.
Table
issueid ,status ,substatus ,cdate
1 ,closed ,mpv ,01/15/2017
1 ,closed ,xyz ,02/19/2017
1 ,open ,xyz ,04/12/2017
2 ,closed ,xyz , 03/12/2017
2 ,closed ,xyz , 05/12/2017
3 ,closed ,xyz ,01/28/2017
Scenario :
condition1 Exlude the entire issue id group <if status="open" or status="mpv"> and Condition2 distinctcount(issueid) based on max(cdate) for the issue id. Highlighted green is eligible count when aggregated at month level.
Result :
Month, Issue
Jan17, 1
May17, 1
Solution : I was able to get the result by implementing the sceanrio at database level or through DAX query by creating calculated table (condition1) and calculated column rankx function to identify the max date for each issueid(condition2) and finally claculated measure to include condition1 and codition2 :
calculate(distinctcount(issueid),filter(table,issueid<>related(calculatedtable) && maxdate=1))
Just want to check is there any scope to finetune the dax calculation. Any other alternate way to get the same result.
Thank you,
Solved! Go to Solution.
Hi @aj1107
I had some success with this approach. I created a new calcualted table using the following code
Table = VAR ExcludeThese = SUMMARIZE(FILTER('Table1','Table1'[status] ="Open" || Table1[substatus] = "mpv"),Table1[Issueid],"Blank",1) VAR ReturnTable =SUMMARIZE( SUMMARIZE( FILTER( NATURALLEFTOUTERJOIN(Table1,ExcludeThese), [Blank] = blank() ), 'Table1'[Issueid],"Max Date" , MAX('Table1'[cdate]) ), [Max Date], "Issue", DISTINCTCOUNT(Table1[Issueid]) ) RETURN SELECTCOLUMNS(ReturnTable,"Month",FORMAT([Max Date],"MMMYY"),"Issue",[Issue])
Hi @aj1107
I had some success with this approach. I created a new calcualted table using the following code
Table = VAR ExcludeThese = SUMMARIZE(FILTER('Table1','Table1'[status] ="Open" || Table1[substatus] = "mpv"),Table1[Issueid],"Blank",1) VAR ReturnTable =SUMMARIZE( SUMMARIZE( FILTER( NATURALLEFTOUTERJOIN(Table1,ExcludeThese), [Blank] = blank() ), 'Table1'[Issueid],"Max Date" , MAX('Table1'[cdate]) ), [Max Date], "Issue", DISTINCTCOUNT(Table1[Issueid]) ) RETURN SELECTCOLUMNS(ReturnTable,"Month",FORMAT([Max Date],"MMMYY"),"Issue",[Issue])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |