Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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: ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

For that, we will have to write a calculated column formula (not a measure).  Calculated column formulas do not respond to change in slicers.  

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    I do not understand the question.

  • m3tr01d's avatar
    m3tr01d
    Continued Contributor
    5 years ago

    Anonymous You can add a Visual filter on the Whole page and select on these status if you want

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      of course, thank you!