cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsaray
Helper II
Helper II

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
1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @nsaray ,

 

Based on my test, I suggest you create a new table like this:
new table for legend.PNG

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:

final stacked bar chart.PNG

 

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.

View solution in original post

11 REPLIES 11
Eyelyn9
Community Support
Community Support

Hi @nsaray ,

 

Based on my test, I suggest you create a new table like this:
new table for legend.PNG

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:

final stacked bar chart.PNG

 

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.

CNENFRNL
Super User
Super User

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"
    )

Screenshot 2021-07-03 050847.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I need to eventually get to this to be able to graph it as a stacked bar chart

 

DateAuto/Manual Count 
1/01/2021 Partially Auto1
2/01/2021Partially Auto1
2/02/2021Fully Auto1
3/01/2021Manual1
3/01/2021Fully Auto1

ok thanks, so do i just create a calculated column instead of a measure, is it the same formula?

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?

m3tr01d
Continued Contributor
Continued Contributor

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

of course, thank you!

I do not understand the question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors