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,

You may download my PBI file from here.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for your suggestions

     

    Is it possible to then graph it as a stacked bar chart .. 

    So per day, can I get a count of Partially Auto, Fully Auto and Manual

     

    Sorry I'm quite new so your help is very much appreciated

    • 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?