Forum Discussion

AK92's avatar
AK92
Regular Visitor
2 years ago
Solved

Dynamic M Query - Pass SQL query as a parameter

Hi Anonymous

 

Looking for some expertise on a scenario that I'm trying to figure out. Any quick help is appreciated as this has an impact in the architecture.

 

Source: ADB - Tables

Connectivity: Direct query mode

Scenario:

-I’m developing a PBI report for source data validation based on multiple test cases. 

-Source data is being validated through test cases(ADF) and few other ETL steps 

-Each test case is on different columns/tables and conditions and is written in SQL 

-Failed records are stored in a table(ADB) with fields like qc_id, qc_type, etc.,.

 

Sample data:

 

qc_idtest_query
1select col1, col10 from #table where sales < 0
2select col3, col4 from #table where quantity is null

 

PBI Requirement:

User wants to see the dynamic table as output as per the slicer selection on qc_id.

Eg.,

Slicer - qc_id

When qc_id '1' is selected from the dropdown, the respective test query (select col1, col10 from #table where sales < 0) should be executed and return the specified columns

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi AK92 ,

    I create a table as you mentioned.

    Then I create a new table and here is the DAX code.

    Table 2 = 
ADDCOLUMNS (
    FILTER (
        'Table',
        'Table'[sales] > 0
    ),
    "qcid", 'Table'[qc_id],
    "col1_1", 'Table'[col1],
    "col10_1", 'Table'[col10],
    "sales_1", 'Table'[sales],
    "SalesFlag", 1
)

    So when you use a slicer, it will give you what you want.

     

     

     

    Best Regards

    Yilong Zhou

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi AK92 ,

    I create a table as you mentioned.

    Then I create a new table and here is the DAX code.

    Table 2 = 
ADDCOLUMNS (
    FILTER (
        'Table',
        'Table'[sales] > 0
    ),
    "qcid", 'Table'[qc_id],
    "col1_1", 'Table'[col1],
    "col10_1", 'Table'[col10],
    "sales_1", 'Table'[sales],
    "SalesFlag", 1
)

    So when you use a slicer, it will give you what you want.

     

     

     

    Best Regards

    Yilong Zhou

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