Forum Discussion

FSMS's avatar
FSMS
Frequent Visitor
2 years ago
Solved

Converting SQL to DAX URGENT HELP NEEDED

select m.id, count(distinct a.id) total_calls from moment m join moment_keywords k on k.moment_id = m.id join moment_mapping mp on mp.moment_keyword_id = k.id join transcription t on t.id = mp.tr...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi FSMS ,

    You can create a measure as below to get it:

    Measure =
VAR _mkids =
    CALCULATETABLE (
        VALUES ( 'moment_keywords'[id] ),
        FILTER ( 'moment_keywords', 'moment_keywords'[moment_id] = 215 )
    )
VAR _tids =
    CALCULATETABLE (
        VALUES ( 'moment_mapping'[transcription_id] ),
        FILTER ( 'moment_mapping', 'moment_mapping'[moment_keyword_id] IN _mkids )
    )
VAR _aids =
    CALCULATETABLE (
        VALUES ( 'transcription'[audio_id] ),
        FILTER ( 'transcription', 'transcription'[id] IN _tids )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'audio'[id] ),
        FILTER ( 'audio', 'audio'[id] IN _adids )
    )

    Best Regards

FSMS's avatar
FSMS
Frequent Visitor
2 years ago

This is the wrong solution, i am unable to type this part of the dax ['audio'[id]
)
)
)]

only fields from moment table is being displayed 

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

I understand your concern. You're trying to create a measure in Power BI that counts the distinct id values from the audio table associated with a specific moment ID.

Let's break down the problem and solve it step by step.

Firstly, ensure that the relationships between your tables (moment, moment_keywords, moment_mapping, transcription, and audio) are correctly set up in the Power BI model view.

Once the relationships are set up, here's how you can write the DAX measure:

 

Total Calls = CALCULATE( COUNTROWS( SUMMARIZE( FILTER( ALL('audio'), RELATEDTABLE('transcription'), RELATEDTABLE('moment_mapping'), RELATEDTABLE('moment_keywords'), RELATEDTABLE('moment')[id] = 215 ), 'audio'[id] ) ) )

 

In this DAX expression:

  1. RELATEDTABLE is used to follow the relationships between tables.
  2. FILTER is used to filter the audio table based on the relationship with other tables.
  3. SUMMARIZE is used to group by the id from the audio table, which should give you distinct id values.
  4. COUNTROWS then counts these distinct id values.

After creating this DAX measure, you can use it in a card visual to display the total distinct call IDs for moment ID 215.

Make sure that the relationships between your tables are set up correctly for this DAX expression to work as expected.