Converting SQL to DAX URGENT HELP NEEDED

[FSMS]

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.transcription_id
join audio a on a.id = t.audio_id
where m.id = 215
group by m.id

can someone help me convert this SQL code into DAX . I have a total of 5 tables starting from moment table until audio table and has many to one relationships between all of them.
im looking to find distinct count of callids where a single moment id=215 for a card visual.

[FSMS]

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 

[Dangar332]

hi, @FSMS 

can you put screenshort of data model?

[123abc]

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.

[Dangar332]

try below

measure =
countx(
  filter(
     summarizecolumn(moment[id],
                  moment_keyword[id],
                  moment_mapping[id],
                  transaction[id],
                  audio[id]
     ),
     moment[id]= 215
   ),
   audio[id]
)

[AntrikshSharma]

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

[FSMS]

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

[Anonymous · Accepted solution]

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]

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.

[FSMS]

Thank You this worked great!

[123abc]

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.