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
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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