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

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
2 years ago

try below

measure =
countx(
  filter(
     summarizecolumn(moment[id],
                  moment_keyword[id],
                  moment_mapping[id],
                  transaction[id],
                  audio[id]
     ),
     moment[id]= 215
   ),
   audio[id]
)
  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    2 years ago

    SUMMARIZECOLUMNS doesn't work in a measure or calculated columns or inside another SUMMARIZECOLUMNS

    • FSMS's avatar
      FSMS
      Frequent Visitor
      2 years ago

      can anyone help me get the final solution? or possibly any changes to the schema to make it better ?

      • 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