How to create TOP1 measure to display the result in card visualization

[Nico31]

Hello, 

I'm trying to create measure, which will give me only TOP1 ANSWER_VALUE from the Dim_Answer table to display it in the card visualization. Top1 should be based on a measure, which calculates survey results in the company. 

Currently my measure look like this and I'm using TOPN with values function to return what I want, but it is not working. I would really appreciate your help with this. 

TOP 1 %Reason_Joining =

VAR _JoiningCount =

    CALCULATE (

        [Participants],

        Fact_Survey_Results[QUEST_ID] = 41,

        Fact_Survey_Results[Question_Number] = 34

    )

VAR _JoiningTotal =

    CALCULATE (

        [Participants],

        ALL ( Dim_Answer ),

        Fact_Survey_Results[QUEST_ID] = 41,

        Fact_Survey_Results[Question_Number] = 34

    )

VAR _Result =

    DIVIDE ( _JoiningCount, _JoiningTotal )

RETURN

    TOPN(1, VALUES(Dim_Answer[ANSWER_VALUE]),_Result)

[amitchandak]

@Nico31 , TOP 1 is based on dim value

Like TOP city based on sales

TOPN(1,all(Geography[City]),[Sales],DESC),

Sales of top city

CALCULATE([Sales], keepfilters(TOPN(1,all(Geography[City]),[Sales],DESC) ) )

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

You can also use index function

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

[Nico31]

I would like to get sth like your 'TOP city based on sales' and display my TOP1 'ANSWER_VALUE' in the card visalization. I adjusted my DAX after the RETURN statement like this: 

TOP 1 %Reason_Joining = 
VAR _JoiningCount =
    CALCULATE (
        [Participants],
        Fact_Survey_Results[QUEST_ID] = 41,
        Fact_Survey_Results[Question_Number] = 34
    )
VAR _JoiningTotal =
    CALCULATE (
        [Participants],
        ALL ( Dim_Answer ),
        Fact_Survey_Results[QUEST_ID] = 41,
        Fact_Survey_Results[Question_Number] = 34
    )
VAR _Result =
    DIVIDE ( _JoiningCount, _JoiningTotal )
RETURN
    TOPN(1, all(Dim_Answer[ANSWER_VALUE]),_Result,DESC)

but when trying to use this measure in card visualization I'm getting this message 

[Anonymous]

Hi @Nico31 ,

The return value of TOPN is a table, but the card visualization in Power BI expects a single value.
TOPN function (DAX)

Please try this:

TOP 1 %Reason_Joining = 
VAR _JoiningCount =
    CALCULATE (
        [Participants],
        Fact_Survey_Results[QUEST_ID] = 41,
        Fact_Survey_Results[Question_Number] = 34
    )
VAR _JoiningTotal =
    CALCULATE (
        [Participants],
        ALL ( Dim_Answer ),
        Fact_Survey_Results[QUEST_ID] = 41,
        Fact_Survey_Results[Question_Number] = 34
    )
VAR _Result =
    DIVIDE ( _JoiningCount, _JoiningTotal )
VAR TopAnswerTable =
    TOPN(1, all(Dim_Answer[ANSWER_VALUE]),_Result,DESC)
RETURN
    MAXX(TopAnswerTable, [ANSWER_VALUE])

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Nico31]

Hi @Anonymous ,

Thanks for your answer. Unfortunately it's not working. It's giving me completely random result in a card visual right now. The result doesn't change regardless of whether the order is ASC or DESC. 

The correct top1 answer in this case should be "Good development opportunities", which is above 50% when it comes to _Result measure 

VAR _Result =
    DIVIDE ( _JoiningCount, _JoiningTotal )

I think that it may be related to relations in the model. 'Participants' measure that I use in the DAX expression above looks like this: 

Participants:= CALCULATE(DISTINCTCOUNT(Fact_Survey_Results[RESPONDENT_ID]))

There is FACT table (FACT_Survey_Results) which contains columns 'QUEST_ID', 'Question_Number' and 'RESPONDENT_ID'. There is also one dimension table (Dim_Answer), which contains column 'ANSWER_VALUE' and this table is connected with Fact table using 1 to many relationship and separate columns containing IDs. 

Can you please have a look at it from this perspective?