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
Shamatix
Post Partisan
Post Partisan

Measure Cross tables

Hey fellow power bi users,

 

 

I currently have 2 tables, table A and table B, table A has a column with TRUE and FALSE values, in table B i want to create a measure that points towards table As column saying something like (If TableA.Column = true, "Approved","Failed") so basicly a column that writes Approved every time Table As column return True and "Failed" everytime it Table As column returns "False" but I want this measure to be in Table B, how do I obtain this result?

Best regards

11 REPLIES 11
v-yuezhe-msft
Employee
Employee

@Shamatix,

Firstly, merge the two tables in Query Editor.
1.JPG

Secondly, expand the true/false column in the merged table.
2.JPG

Thirdly, create a calculated columns using DAX below.
Column = IF(Merge2[A.True/false]=TRUE(),"Approved","Failed")
3.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GilbertQ
Super User
Super User

Hi @Shamatix

 

Do you perhaps have an example?

 

From your description it appears that all the data and measure is happening in Table A?

 

What is the relationship between the tables? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

It's a many to many relationship so I have created a relationship column between em called "IKEY", all my other columns are in table B and I would prefer if I also could have the measure in table B instead of A:P

What happens if you try and create the measure?

 

Measures can be created in any table if there are relationships between the data, so that should not be an issue. As long as the measure is bringing back the right results.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Well I am not to familiar with the synthax on how to do with with a TRUE / FALSE, because usually I use "SUM" but this aint really a SUM and it aint an integer either?

Hi @Shamatix

 

 

What I would do is to create a measure for each of your conditions using the COUNTROWS

 

Approved = CALCULATE(COUNTROWS('Table1'), 'Table1'[ColumnName] = "True")

There might have to be some possible tweaking around the "True" but that should hopefully work, then that will give you the count of times it was Approved = True?

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I dont really want the count, what I want the result to be is end up with a slicer saying "Approved" "Failed" and if I press approved it will only show those rows where the value is "True" and if I pressed "Failed" it would show where its "False" 

Oh ok I would then do that in the Query Editor and create a conditional column based on if it is True or False.

 

Then use that new Conditional Column as your slicer in your data.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Yes but I cant create a conditional column in Table B based on a column in Table A as far as I know (It only shows the column in the table you are creating the conditional column in)

That is correct, as far as I currently know that is a limitation in Power BI.


What you could do after you create it in the one table, is in the Data Model you could create a Calculated Column there and as long as you have your relationships you could then use the RELATED function to bring it into your second table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Well with Related I can only go over to my "IKEY" table, as mentioned I have a many to many relationship so I needed a relationship table in order to create a relationship between the two.

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.