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:

- If Marg ID has 3 statuses for the day

- If the user is Auto for all 3 statuses, output is "Fully Auto"

- If the user is Auto for 1 or 2 of the statuses, output is "Partially Auto"

- If the user is not auto for all 3 statuses (ie it has a user name for all 3 statuses), output is "Manual"

- If Marg ID has 2 statuses for the day

- If the user is Auto for all 2 statuses, output is "Fully Auto"

- If the user is Auto for 1 of the statuses, output is "Partially Auto"

- If the user is not auto for all 2 statuses (ie it has a user name for all 2 statuses), output is "Manual"

 

Can anyone please suggest a DAX measure I could use? Thanks so much!

 

Data

DateMarg ID    Status         User     
1/1/2021    12345IssuedAuto
1/1/202112345AgreedJohn
1/1/202112345FinalisedAmy
2/1/202112345 AgreedAuto
2/1/202112345FinalisedAuto
2/1/202156789IssuedAuto
2/1/202156789AgreedAuto
2/1/202156789FinalisedJohn
3/1/202156789IssuedJohn
3/1/202156789AgreedJohn
3/1/202178912IssuedAuto
3/1/202178912AgreedAuto

 

Output

 DateMarg IDOutput
1/1/2021  12345Partially Auto
2/1/202112345Fully Auto   
2/1/202156789  Partially Auto
3/1/202156789Manual
3/1/202178912Fully Auto
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.

11 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    Output = 
VAR __st = DISTINCTCOUNT( INFO[Status] )
VAR __auto = COUNTROWS( FILTER( INFO, INFO[User] = "Auto" ) )
RETURN
    SWITCH(
        TRUE(),
        __st = __auto, "Fully Auto",
        __auto = 0, "Manual",
        "Partially Auto"
    )

    • 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

    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.