PowerBI Measure

2
votes

I have a powerbi model to analyse the results the of a survey.

I have the following calculated measures:

[distinctcount of respondents this year] = DISTINCTCOUNT('Fact NRPS'[Serial])

[distinctcount of respondents last year] = CALCULATE('Fact NRPS'[Responders], SAMEPERIODLASTYEAR('Dim Dates'[Date]))

[year on year difference] = [distinctcount of respondents this year] - [distinctcount of respondents last year]

In the report I summarise these measures by [Question]. So I have a report with columns like this:

[Question],[distinctcount of respondents this year],[distinctcount of respondents last year],[year on year difference]

Now I would like to add a summary line to the bottom of this report showing the number of questions where the [year on year difference] is greater than zero and where the [year on year difference] is less than zero.

So it would tell us something like 45 questions have more respondents compared to last year, and 21 have less.

How could I do this in DAX?

  • I tried creating a measure using SUMMARISE to group the data by [Question] and getting the number of questions where [year on year difference] is greater than zero. However, this did not work. Because in summarise it does not allow me to use another calculated measure i.e. [year on year difference].

  • If I add a calculated measure such as If([year on year difference]>0,1,0), and then add a total row to the report, this does not work either. In this case it shows this calculated measure as 1 or 0 for the total row.

Any ideas on how I can go about doing this?

Thanks

Mehmet

1
powerbidax
This is an old question, so you're probably not looking for an answer anymore. If you are, do you have any sample data you can share for each table? It'd also be helpful to know how the tables are related in Power BI (e.g. a screenshot of the relationship screen). – Leonard

1 Answers

1
votes

Something like this should work:

Measure = CALCULATE(COUNT([Column]), 
                    FILTER(Table, [Year]=2017)) - CALCULATE(COUNT([Column]),
                    FILTER(Table, [Year]=2016))