Forum Discussion
Help with DAX Measure
Hi I have data similar to the below: - Marg ID can appear on multiple days - Marg ID can either go through 2 or 3 statuses per day - What I need to count is - For each day, for each Marg ID: ...
Hi Anonymous ,
Based on my test, I suggest you create a new table like this:
Then use the following formula to create measures:
1. For stacked bar chart:
count by date and type = VAR _t = ADDCOLUMNS ( DISTINCT ( SELECTCOLUMNS ( 'Data', "date", 'Data'[Date], "id", 'Data'[Marg ID] ) ), "Type", [Measure] ) RETURN COUNTX ( FILTER ( _t, [Type] = MAX ( 'Table(for legend)'[Value] ) ), [date] )2. For table:
count = CALCULATE(DISTINCTCOUNT(Data[Date]),FILTER('Data',[Measure]=MAXX('Data',[Measure])))The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, I also realised I need to filter only on where Status is Issued, Agreed or Finalised. There seems to be another 2 statuses that are appearing in the data that need to be filtered out. Are you able to account for this requirement as well?
Anonymous You can add a Visual filter on the Whole page and select on these status if you want
of course, thank you!