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

Anonymous's avatar
Anonymous
Not applicable
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

v-yiruan-msft can you modify this formula to get count from 30 days ago for the same moment? I wanted to visualise change in values over time to show growth or decay . The datefield is called calltime and is present in the dev_audio table.