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
maibacherstr
Helper III
Helper III

Compare Count of String Between Two Tables Given a Status Column

Hello, I'm stuck on the following measure. I have two tables, each with a Status column, and need to compare the number of "Operational" rows for each value in the Type column (in this example Type = Car or Truck). The output should support a conditional format being added into a table visual. I've already related the tables in the data model. Here's just a bit more detail and the problem statement:

 

maibacherstr_0-1661444602864.png

 

Thank you in advance!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @maibacherstr 
Apologies for the late reply. Please refer to attached sample file.

1.png

View solution in original post

17 REPLIES 17
tamerj1
Super User
Super User

Hi @maibacherstr 
Apologies for the late reply. Please refer to attached sample file.

1.png

You're a hero to the people, sir!

 

The sample report took a little finagling (deleted then re-add the column to the table, for no good reason) before it worked properly, but my live report worked on the first try.

 

THANK YOU SO MUCH for your time spent, your persistence and for sharing your expertise. Kudos awarded. Many thanks again.

tamerj1
Super User
Super User

Hi @maibacherstr 

any relationship between the two tables? Any bridge table?

Hi @tamerj1 thanks for starting to think this over. There is no bridge table, but as mentioned the two tables are already related -- they use the Type columns as keys. 

Since the tables come from two very different data sources, one being extremely limited for its columns, there is no ID column or other unique values available for building that relationship.

 

Thanks again

@maibacherstr 

please try

CALCULATE (

    COUNTROWS ( TableA ),

    TableA[Status] = "Operational" 

)

Same for TableB

@tamerj1 It seems like there are some gaps between this measure and being about to compare both counts, such that if there is a difference between the two, then the result of the measure enables a conditional format. What am I missing, please?

@maibacherstr 

Not sure I fully understand that. If the difference between the two measures equals 0 then "green" otherwise "red". 

Hello @tamerj1 I understand the fundamentals of the difference problem, but still the question of how specifically to implement it. How many measures will be used? If 1, will it require the =related() function to be used, or something like that? Thank you

Hi @maibacherstr 
You can create a difference measure to be used in the conditional formating as follows

1.png

2.png

Table A Operational = 
CALCULATE (
    COUNTROWS ( 'TABLE A' ),
    'TABLE A'[Status] = "Operational" 
)
Table B Operational = 
CALCULATE (
    COUNTROWS ( 'TABLE B' ),
    'TABLE B'[Status] = "Operational" 
)
Difference = [Table A Operational] - [Table B Operational]

 

@tamerj1 thanks very much for posting so much detail. There is one last detail that I posted incorrectly, which is that TableB actually contains TotalOperationalQuantity in the rows, such as: Cars .... 12 Operational ; Trucks .... 9 Operational. Table A was described correctly, where 1 row = 1 record which why you wisely suggested using a countrows function. But Table B actually contains a sum in each row:

 

Table B:

maibacherstr_0-1661781695124.png

Therefore Measure B still needs some work before the 3rd measure -- the subtraction equation -- will enable conditional formats to behave properly. Currently, my conditional formats are set up exactly as yours are.

 

Many thanks again

I thought the measure should be super easy, but the Difference equation is not producing the desired formatting behavior... Here's what I'm trying to use:

 

MeasureB = SUM(TableB[TotalOperationalQuantity])

Hi @maibacherstr 

can you please share a relevant sample of data and advise the expected results based on it?

Hello @Tamer, I just learned that .xlsx files can't be uploaded here --? After doing my best to recreate a useful dataset example. Unfortunately the full dataset is not mine to share, but I hope the following can still come in helpful. I suspect my MeasureB is the problem.

 

Many thanks again, I hope this screenshot is still useful.

 

maibacherstr_0-1661879665628.png

 

Hi @maibacherstr 

you can upload to WeTransfer, OneDrive, dropbox or any other service and share the download link. 

Hi @tamerj1 this obviously took a little time to get situated. But the link to a sample report should be available to you via OneDrive below. I recreated the report very carefully in a sample set. 

 

I'm certain that Measure B needs some work. I have a strong feeling that with your expertise this could be solved very quickly. Here's a quick preview, and the link at the bottom.

 

Eager to award kudos; as before, I *greatly* appreciate your time, energy and expertise.

 

maibacherstr_0-1663949333750.png

Hi @maibacherstr 

am using my phone right now so I cannot see the pbix file. What is the dax for measure B? 
count snd total are measures or aggregated columns? Measures what is the dax if aggregated columns then what type of aggregation?

Measure B is still the same as I had it originally: 

 = SUM(TableB[TotalOperationalQuantity])

 

But the sum should only apply to the status value, "Operational". I'm not sure I follow your other questions.

 

Thank you

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.

Top Solution Authors