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

Be 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

Reply
maruthi
Frequent Visitor

Data Modelling Challenge

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:

Fact_TableFact_TableDim_SpeakerDim_SpeakerActual OutputActual OutputExpected OutputExpected Output

 When I create a simple table, I get the result as per image - "Actual Output".

Data modellings looks like below:

 

Data ModelData Model

How can I achieve as per "Expected Result " image? Any suggestions are really appreciated.

Thanks in advance.

 

Kind Regards,

Maruthi

 

1 ACCEPTED 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!!!

View solution in original post

4 REPLIES 4
PietroFarias
Resolver II
Resolver II

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:

DAX PowerPivot - Excel

MarkS
Resolver IV
Resolver IV

Hi @maruthi,

I would suggest that you use the query editor to get your fact_table to look like this

community 04-11-2018 table.PNG

 

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.

maruthi
Frequent Visitor

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!!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.