Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated Columns and Measure

Hi,

 

I'm trying to create a measure that looks at columns Journey Alias and Stop Comp over 5 mins and returns either Compliant or Not Compliant for a specific Journey Alias.

 

Eg. 101191351 should be Not Compliant for all 3 rows, how am I able to make this?

iampaoloc_1-1611884562093.png

 

Thanks

 

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

You could  try the following measure:

test =
VAR not_compliant =
    CALCULATE (
        COUNT ( 'Table'[Stop Comp over 5 mins] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Journey Alias] = MAX ( 'Table'[Journey Alias] )
                && 'Table'[Stop Comp over 5 mins] = "Not Compliant"
        )
    )
VAR at_all =
    IF ( not_compliant >= 1"Not Compliant""Compliant" )
RETURN
    at_all

 

 

Finally you will see:

v-luwang-msft_0-1612261236529.png

 

 

If this is not what you want, please describe what you want, thank you!

 

 

Best Regards,

Lucien

 

View solution in original post

14 REPLIES 14
v-luwang-msft
Community Support
Community Support

Hi  @Anonymous  ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.
 

Best Regard

Lucien Wang

v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

You could  try the following measure:

test =
VAR not_compliant =
    CALCULATE (
        COUNT ( 'Table'[Stop Comp over 5 mins] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Journey Alias] = MAX ( 'Table'[Journey Alias] )
                && 'Table'[Stop Comp over 5 mins] = "Not Compliant"
        )
    )
VAR at_all =
    IF ( not_compliant >= 1"Not Compliant""Compliant" )
RETURN
    at_all

 

 

Finally you will see:

v-luwang-msft_0-1612261236529.png

 

 

If this is not what you want, please describe what you want, thank you!

 

 

Best Regards,

Lucien

 

Anonymous
Not applicable

This doesn't work either, if you can help me share the file link then that would help as having the underlying data will help make my question easier to understand.

Hi, @Anonymous ,

Wish  it is useful for you.

please click here.

 

Best Regards,

Lucien

Anonymous
Not applicable

Hi Lucien,

 

How can I do that so I can share my report to you instead?

 

Regards,

Paolo

Hi @Anonymous ,

You can upload the .pbix file  after removing sensitive data  to onedrive business and share the link with me.

Ashish_Mathur
Super User
Super User

Hi,

I think the ALLEXCEPT() function should work.  I can offer more help if you share the link from where i can download your PBI file.


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

Hi, 

I'm not sure how to do this actually since I've never shared a powerbi file to an external user. I've published it in my workspace and clicked share there but it asks for an email address and the only link available was the report link at the bottom which I have already sent here.

 

If you can direct me on how to do this then I'll be able to do so since I can't find a solution for this on here.

Anonymous
Not applicable

That is not a file download link.


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

@Anonymous , Try this one

if(isblank(calculate(countx(filter(Table, containsstring(Table[Stop over 5 Min], "Not Complaint")),Table[Journey Alias]), filter(allselected(Table), Table[Journey Alias] =max(Table[Journey Alias])))),"Complaint","Not Complaint")

amitchandak
Super User
Super User

@Anonymous , Try a measure like

if(isblank(calcuate(countx(filter(Table, containsstring(Table[Stop over 5 Min], "Not Complaint")),Table[Journey Alias]), allexcept(Table, Table[Journey Alias]))),"Complaint","Not Complaint")

Anonymous
Not applicable

this measure seems to inflate the number of actual rows in the table, there's only meant to be 40 rows and this just keeps going when I scroll down and all rows turn into Not Compliant, unless I filter the Journey Aliases one by one then the measure works

iampaoloc_0-1611886624237.png

 

@Anonymous ,

Try this one

if(isblank(calculate(countx(filter(Table, containsstring(Table[Stop over 5 Min], "Not Complaint")),Table[Journey Alias]), filter(allselected(Table), Table[Journey Alias] =max(Table[Journey Alias])))),"Complaint","Not Complaint")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.