Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Pivot table as a source table

Hello,   I am new to the DAX and will need some support by you. The problem I want to solve is described on the picture attached. Thank you in advance.
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Create the following table:

     DistinctCount = GENERATESERIES(0; 200; 1)

    The second argument can be change to the maximum value you  want, it doesnt matter the number since the next measure will filter out this table.

     

    Create the following measure:

    User Count histogram=
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( Table1; Table1[Users]; "CountOfUsers"; COUNT ( Table1[Users] ) );
            [CountOfUsers] = SELECTEDVALUE ( 'DistinctCount'[DistinctCount] )
        )
    )
)

    Now use the column of DistinctCount  table on your X-Axis and the measure on your values this will allow you to have slicers on dates and on users.

     

    Be aware that you can define if the values without data are viewed in the X-Axis changing from categorical to continuous on the options of the visual

     

    Check PBIX file attach.

     

    This solution is based on this blog post from PowerPivot(Pro).

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
7 years ago

Hi Anonymous ,

 

To my understandi you want to create a new table using DAX that gets distinct values for names and the count of total calls correct?

 

Try this code to do a new table:

Table =
CALCULATETABLE (
    SUMMARIZE ( ALL ( Table[Indv Name] ); Table[Indv Name]; "TotalCalls"; COUNT ( Table[Indv Name] ) )
)

Be aware that this new table does not have in consideration any other filters like time or something similar to interact with the rest of the report, so if you for example do a slicer for time it will not change the results of this table

 

Regards,

MFelix

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi MFelix,

     

    You are right that this way I will not have time filtering this way will sum all calls for all periods for this for this Indv Name.

    I would like to assign the total calls for each distinct Indv Name for the selected period of time. When I select for example 01.2019 to 03.2019, I would like to see column with Indv Name (distinct) and its total calls calculated based on this.

     

    The way PowerBI doing it like pivot table in excel is what I need to group by distinct Indv Name and its total calls from the main table.

    In raw data for on each date have Indv Name (repeated) and TotalCall column which is always 1 as value in the cell.

     

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi Anonymous ,

       

      Create the following table:

       DistinctCount = GENERATESERIES(0; 200; 1)

      The second argument can be change to the maximum value you  want, it doesnt matter the number since the next measure will filter out this table.

       

      Create the following measure:

      User Count histogram=
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( Table1; Table1[Users]; "CountOfUsers"; COUNT ( Table1[Users] ) );
            [CountOfUsers] = SELECTEDVALUE ( 'DistinctCount'[DistinctCount] )
        )
    )
)

      Now use the column of DistinctCount  table on your X-Axis and the measure on your values this will allow you to have slicers on dates and on users.

       

      Be aware that you can define if the values without data are viewed in the X-Axis changing from categorical to continuous on the options of the visual

       

      Check PBIX file attach.

       

      This solution is based on this blog post from PowerPivot(Pro).

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi MFelix 

         

        Thank you so much for your help. It is working and it's great.  I made some changes to meet my data requerements. Here is the sample DAX:

         

        # of CustomersX =
        CALCULATE (
        COUNTROWS (
        FILTER (
        SUMMARIZE ( Table, Table[CustomerID], "CountOfUsers", DISTINCTCOUNT(Table[CallsID]),

        [CountOfUsers] = SELECTEDVALUE ( 'DistinctCount'[DistinctCount] )
        )
        )
        )+0

         

        I put +0 at the end to start the line from 0 on x-axis.

         

        Thank you again.