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

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

To convert your SQL query to a DAX formula, you'll want to use DAX functions to replicate the logic. In DAX, you can use COUNTROWS and related functions to achieve this. Here's how you can approach it:

  1. You'll want to create relationships between your tables if they don't already exist. Make sure the relationships are many-to-one from the moment table to the moment_keywords, then to moment_mapping, then to transcription, and finally to audio.

  2. Once relationships are set, you can use the following DAX measure:

Total Calls =
VAR SelectedMomentID = 215
RETURN
CALCULATE(
COUNTROWS(
SUMMARIZE(
FILTER(
ALL('moment'),
'moment'[id] = SelectedMomentID
),
'audio'[id]
)
)
)

 

Here's a breakdown of what's happening:

  • FILTER(ALL('moment'), 'moment'[id] = SelectedMomentID): This part filters the moment table to only include rows where the ID is 215.

  • SUMMARIZE(..., 'audio'[id]): This part groups the data by the audio ID, ensuring we get distinct counts.

  • COUNTROWS(...): Counts the number of rows after summarizing.

You can then use the Total Calls measure in your card visual to display the distinct count of call IDs for the moment ID 215.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.