March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi All,
I am trying to achieve a simple report in Power BI. I have attached a sample images for reference.
Fact_Table,Dim_Speaker data looks like below:
When I create a simple table, I get the result as per image - "Actual Output".
Data modellings looks like below:
How can I achieve as per "Expected Result " image? Any suggestions are really appreciated.
Thanks in advance.
Kind Regards,
Maruthi
Solved! Go to Solution.
Hi @MarkS,
As per your suggestion, I tried to unpivot data and updated dashboard. It's working now. Thanks for your suggestion.
@PietroFarias,I will try your solution and let you know. Thanks for your reply.
Have a nice day!!!
Another way, can be done by DAX. Below is the DAX used to generate this table. And then a link to an Excel file with the result.
CountReach := SUMX ( SUMMARIZE ( Dim_Speaker; Dim_Speaker[SpeakerName]; "Total"; SUMX ( FILTER ( Fact_Table; Fact_Table[Speaker1name] = Dim_Speaker[SpeakerName] || Fact_Table[Speaker2name] = Dim_Speaker[SpeakerName] || Fact_Table[Speaker3name] = Dim_Speaker[SpeakerName] ); Fact_Table[Reach] ) );[Total] )
Donwload sample:
Hi @maruthi,
I would suggest that you use the query editor to get your fact_table to look like this
I did that with this code:
let
Source = (enter Source Table here)
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Reach", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "Reach"}, "Attribute", "Value"),
#"Extracted Text Range" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Middle(_, 7, 1), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Range",{{"Attribute", "Speaker Number"}, {"Value", "SpeakerName"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Speaker Number", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([SpeakerName] <> ""))
in
#"Filtered
(note I did not enter the email columns when copying your sample data as it was redundant so I would delete it, and I changed the date so that it would not produce errors on my computer when I changed the date column to Date type)
Then your data will be in a shape that Power BI can quickly and efficently evaluate over and compress, and your DAX formulas will be easier to write and the output you get will be what was expected.
Hi @MarkS,
I will try to follow as per your suggestion. In between, is there any way to attach a pbix file in this post?
Kind Regards,
Maruthi
Hi @MarkS,
As per your suggestion, I tried to unpivot data and updated dashboard. It's working now. Thanks for your suggestion.
@PietroFarias,I will try your solution and let you know. Thanks for your reply.
Have a nice day!!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
170 | |
145 | |
90 | |
67 | |
58 |