Forum Discussion
Dynamic queries
No, the columns Will be different per indicator.
jvandyck technically it is possible.
But how painful the development of that would be, depends on how many different indicators you have.
To the best of my knowledge, you can create Dax measures and viz for each indicator (slicer selection) and save them as bookmark. Depending on what users select, you can connect those bookmarks to each indicator (slicer selection) and which is how you can enable generating diffrent query as per each indicator selection.
But if you had the same set of columns regardless the indicators, where the only difference was the filtering, e.g
WHERE [fl_nt_npsn_adr_email_juist_patroon] = 1 OR [fl_nt_npsn_adr_email_ingevuld] = 1you could have created Dax measures and a SWITCH stament to hook up that to the slicer selection without creating bookmarks.
Just to explain....there will be a number of indicators. The exact number I do not know upfront.
Each indicator will be based on a number of flags. The number of flags linked to an indicator can range from 1 to x. Right now all the indicators and flags are in one table as separate columns. If I would go for the bookmark solution, the number of rows would explode, and it would require work each time indicators and flags are added. The row explosion would be because I would have to unpivot all the flag/indicator columns
I have added a better example, including the bookmark approach: https://acerta-my.sharepoint.com/:u:/r/personal/joos_van_dyck_acerta_be/Documents/Share/New%20folder/DQ%20Test%20MU.pbix?csf=1&web=1&e=cG7mtd
However I would really like to make it more flexible. I would like to generate the flag query based on the KPI info datasource instead of bookmarks which contains the filter settings. Eg the query for KPI001 would be:
SELECT base.* , fl.*
FROM [DQ_Framework].[Draft].[ONDERNEMING_CKB_BASE] base
,[DQ_Framework].[Draft].[ONDERNEMING_CKB_BASE_FLAG] fl
where 1=1
and base.nk_ODN_IDEN_NO = fl.nk_ODN_IDEN_NO
and not (fl.[FL_PTY_UP_FMT_NM_INGEVULD] = 1 and fl.[FL_PTY_UP_FMT_NM_STARTS_BLANCK]=0 )Ideally it should also be possible to use the kpi info table to indicate the columns in the select.
So what it comes down to is the following: I have KPI data which is a calculation based on flags. Both the KPI an the flags link to the base table, which contains customer master data. When In the report one selects a KPI, the report should show the KPI data as well as the flag data. The flag data shown is different for each KPI and I want to make this dynamic so I can easily add KPI's. Please let me know if you want me to explain further?
