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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
markefrody
Post Patron
Post Patron

1 Table/Matrix Visual for Calculated Columns and Data from Another Table

Hi,

 

I want to create one table/matrix visualization using calculated columns in table 1 and data from another table (table 2).

markefrody_1-1682751649907.png

 

The output of the table/matrix visualization would look like this:

markefrody_2-1682751689914.png

Any help that you can provide in creating this table/matrix would be greatly appreciated.


Thanks and best regards,
Mark V
 

1 ACCEPTED SOLUTION

Hey @markefrody ,

 

you could use a measure that is checking for the current department and then returning the value from the desired column:

Measure = 
IF( 
    HASONEVALUE('Table 1 Actual'[Department]),
    SWITCH(
        MAX( 'Table 1 Actual'[Department] ),
        "Finance", SUM( 'Table 1 Actual'[Finance Urgent Measure]),
        "HR", SUM('Table 1 Actual'[HR Non-Urgent Measure]),
        "Operations", SUM( 'Table 1 Actual'[Operations Urgent Measure] )
    )
)

 

However, I personally wouldn't do a calculated column.
You could also solve that in only one measure:

Sum Measure =
CALCULATE (
    SUM ( 'Table 1 Actual'[Score] ),
    KEEPFILTERS (
        ( 'Table 1 Actual'[Department] = "Finance" && 'Table 1 Actual'[Type] = "Urgent" )
        || ( 'Table 1 Actual'[Department] = "HR" && 'Table 1 Actual'[Type] = "Non-Urgent" )
        || ( 'Table 1 Actual'[Department] = "Operations" && 'Table 1 Actual'[Type] = "Urgent" )
    )
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

View solution in original post

5 REPLIES 5
markefrody
Post Patron
Post Patron

@selimovd 
Below is a similar sample file

https://www.dropbox.com/s/34tv3pumrnn8e81/Sample%20File.pbix?dl=0

Current table visual looks like this:

markefrody_0-1682827751224.png

I would like to have this output:

markefrody_1-1682827878603.png

Best regards,
Mark V

Hey @markefrody ,

 

you could use a measure that is checking for the current department and then returning the value from the desired column:

Measure = 
IF( 
    HASONEVALUE('Table 1 Actual'[Department]),
    SWITCH(
        MAX( 'Table 1 Actual'[Department] ),
        "Finance", SUM( 'Table 1 Actual'[Finance Urgent Measure]),
        "HR", SUM('Table 1 Actual'[HR Non-Urgent Measure]),
        "Operations", SUM( 'Table 1 Actual'[Operations Urgent Measure] )
    )
)

 

However, I personally wouldn't do a calculated column.
You could also solve that in only one measure:

Sum Measure =
CALCULATE (
    SUM ( 'Table 1 Actual'[Score] ),
    KEEPFILTERS (
        ( 'Table 1 Actual'[Department] = "Finance" && 'Table 1 Actual'[Type] = "Urgent" )
        || ( 'Table 1 Actual'[Department] = "HR" && 'Table 1 Actual'[Type] = "Non-Urgent" )
        || ( 'Table 1 Actual'[Department] = "Operations" && 'Table 1 Actual'[Type] = "Urgent" )
    )
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

@selimovd Thanks Dennis. It works! 

selimovd
Super User
Super User

Hey @markefrody ,

 

can you provide a sample file?

Your calculated columns look more like rows, so I'm not sure how the data looks.

 

Best regards

Denis

Hi @selimovd 


It looks like this:

markefrody_0-1682821812482.png


I'm using DAX for that calculated column:

markefrody_1-1682821891060.png

Hope this helps. If not, please let me know. Thanks.

Best regards,
Mark V

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.