Forum Discussion

maruthi's avatar
maruthi
Frequent Visitor
8 years ago
Solved

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_TableDim_SpeakerActual OutputExpected Output

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

Data modellings looks like below:

 

Data Model

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

Thanks in advance.

 

Kind Regards,

Maruthi

 

Need Help
  • maruthi's avatar
    maruthi
    8 years ago

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

4 Replies

  • MarkS's avatar
    MarkS
    Resolver IV
    8 years ago

    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.

    • maruthi's avatar
      maruthi
      Frequent Visitor
      8 years ago

      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

       

      • maruthi's avatar
        maruthi
        Frequent Visitor
        8 years ago

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

  • PietroFarias's avatar
    PietroFarias
    Resolver II
    8 years ago

    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