Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Thank you in advance!
Solved! Go to Solution.
Hi @maibacherstr
Apologies for the late reply. Please refer to attached sample file.
Hi @maibacherstr
Apologies for the late reply. Please refer to attached sample file.
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.
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
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?
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
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:
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:
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.
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.
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
User | Count |
---|---|
44 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |