Forum Discussion

IzakVA's avatar
IzakVA
Regular Visitor
2 years ago
Solved

Slicers and calculated table

Hi

 

I created the Number of Consumers by Review Count chart below. It maps the number of consumers on the Y axis against the number of reviews per consumer on the X axis. As an example it shows that 233 consumers had 1 review and 167 consumers had 2 reviews.

My problem is that the chart does not react to the slicers that I have at the top of the page. As an example you can see that the chart shows review counts up to 63, but it should only be up to 1 at the moment since the Reporting Month slicer if filtered for one month only at the moment.

 

I created the following calculated table to find the number of reviews per consumer (ClientSLK).

Consumer Review Summary = var Filtered = CALCULATETABLE(
    Activity,
    Activity[ISP Review] = 1)
RETURN SUMMARIZE(
    Filtered,
    Activity[ClientSLK],
    "Review Count", COUNT('Activity'[ISP Review])
)
 
I used the following measure to map the number of consumers on the Y axis.
Number of Consumers =
    DISTINCTCOUNT('Consumer Review Summary'[ClientSLK])
 
This is what my data model looks like. The slicers from the ReportingMonth, Program, LHD, Provider and Team tables work correctly to filter the data from the Activity table. The Consumer Review Summary calculated table is linked to the Activity table through the ClientSLK field, so this table does filter the consumers correctly with the Activity table, but the problem is that all the records from the activity table are retrieved for those consumers, instead of only the records that are filtered in the activity table by the filters. Linking the Team and Reporting Month tables to this table would have solved the problem, but I can't do this because it is a summary table per Consumer and doesn't have the Team or Reporting Month fields in the table.
What can I do to get this table (and the chart) to filter according to the slicer selections?

 

 
Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    Hi IzakVA ,

     

    To what I can understand you want to have the split done by the number of customers with the review correct?


    Since you are creating a physical table you won't get the calculation to be dynamic and not responding to the slicers on your model since the value is fixed by the table values.

    What you need to do is to have a disconnected table with the numbers (1 to 100 - or more) and then make  a measure that will add those values to the logic.

    Basically pick the calcultion you have but change it to a measure that will be dynamic.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    Hi IzakVA ,

     

    To what I can understand you want to have the split done by the number of customers with the review correct?


    Since you are creating a physical table you won't get the calculation to be dynamic and not responding to the slicers on your model since the value is fixed by the table values.

    What you need to do is to have a disconnected table with the numbers (1 to 100 - or more) and then make  a measure that will add those values to the logic.

    Basically pick the calcultion you have but change it to a measure that will be dynamic.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

    • MadsD's avatar
      MadsD
      Icon for Advocate I rankAdvocate I
      1 year ago

      What do you mean by this: What you need to do is to have a disconnected table with the numbers (1 to 100 - or more) and then make  a measure that will add those values to the logic?

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        1 year ago

        MadsD ,

        To have this working in the proper conditions you need to influence the result of the measure by the number that you want in this case they want to present all the values that are up to 63, so it needs to have a disconnected table where the numbers for the slicer will be for usage in the slicer and then on the measure theres is the need to have that selected value to be impacting in the row context.