Forum Discussion

Heinrich's avatar
Heinrich
Post Partisan
1 year ago
Solved

Power BI - DAX Query

Hello

I have a DAX Query that does not work as supposed

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),
			NOT('CQD'[PSTN Trunk FQDN] IN {"sbc02-id.axa.co.id"})
		)

	VAR __DS0Core = 
		FILTER(
		SUMMARIZECOLUMNS(
			'CQD'[PSTN Trunk FQDN],
			"SumTotal_Stream_Count", CALCULATE(SUM('CQD'[Total Stream Count])),
			"SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]))
		),
			[SumPSTN_NER_Good_Percentage] < 100,
			[Total Stream Count] > 1			
		)
		
	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)

EVALUATE	
	    SELECTCOLUMNS(

        __DS0PrimaryWindowed,   // Use the filtered result set

        "SBC", 'CQD'[PSTN Trunk FQDN],  // Rename 'PSTN Trunk FQDN' to SBC

        "NER", [SumPSTN_NER_Good_Percentage],  // Rename 'SumPSTN_NER_Good_Percentage' to NER
        
        "TOTAL STREAM", [SumTotal_Stream_Count]  // Rename 'SumTotal_Stream_Count' to TOTAL STREAM

        
    )

ORDER BY
	"SBC"

 

When I remove  [Total Stream Count] > 1 then I get a result that filters also  [Total Stream Count] less than 100.
But  [Total Stream Count] should have all results

 

Regards

Heinrich

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    Hello Heinrich 

     

    try this

    DEFINE

        VAR __DS0FilterTable =

            FILTER(

                KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),

                NOT('CQD'[PSTN Trunk FQDN] IN {"sbc02-id.axa.co.id"})

            )

     

        VAR __DS0Core =

            FILTER(

                SUMMARIZECOLUMNS(

                    'CQD'[PSTN Trunk FQDN],

                    "SumTotal_Stream_Count", CALCULATE(SUM('CQD'[Total Stream Count])),

                    "SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]))

                ),

                [SumPSTN_NER_Good_Percentage] < 100

            )

           

        VAR __DS0PrimaryWindowed =

            TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)

     

    EVALUATE   

        SELECTCOLUMNS(

            __DS0PrimaryWindowed,

            "SBC", 'CQD'[PSTN Trunk FQDN],

            "NER", [SumPSTN_NER_Good_Percentage],

            "TOTAL STREAM", [SumTotal_Stream_Count]

        )

     

    ORDER BY

        "SBC"

     

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

3 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    Hello Heinrich 

     

    try this

    DEFINE

        VAR __DS0FilterTable =

            FILTER(

                KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),

                NOT('CQD'[PSTN Trunk FQDN] IN {"sbc02-id.axa.co.id"})

            )

     

        VAR __DS0Core =

            FILTER(

                SUMMARIZECOLUMNS(

                    'CQD'[PSTN Trunk FQDN],

                    "SumTotal_Stream_Count", CALCULATE(SUM('CQD'[Total Stream Count])),

                    "SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]))

                ),

                [SumPSTN_NER_Good_Percentage] < 100

            )

           

        VAR __DS0PrimaryWindowed =

            TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)

     

    EVALUATE   

        SELECTCOLUMNS(

            __DS0PrimaryWindowed,

            "SBC", 'CQD'[PSTN Trunk FQDN],

            "NER", [SumPSTN_NER_Good_Percentage],

            "TOTAL STREAM", [SumTotal_Stream_Count]

        )

     

    ORDER BY

        "SBC"

     

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Thankyou, pankajnamekar25, for your response.

    Hi Heinrich,

    We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

    Please find attached the screenshot and the PBIX file, which we hope will assist you in resolving the issue:

    If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will help other community members who might be facing similar queries.

    Should you have any further questions or require additional assistance, please feel free to contact the Microsoft Fabric Community.

    Thank you.

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    Hi Heinrich ,

     

    If you're trying to filter a table to only return rows where Status = "Active", you can use something like this:

    FILTER (
    'YourTable',
    'YourTable'[Status] = "Active"
)

    Or if you're using it inside a measure or calculated table, you might need to wrap it with something like CALCULATETABLE:

    CALCULATETABLE (
    'YourTable',
    'YourTable'[Status] = "Active"
)

    Make sure:

    • The column name is spelled exactly as it appears in your model (case doesn’t matter, but spaces and typos do).
    • The value "Active" matches exactly what's in your data — sometimes there are extra spaces or different casing.

    Let me know if you're using this inside a measure, calculated column, or visual — the context can change how the DAX behaves.

     

    If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
    translation and formatting supported by AI