DirectQuery - Need to Count occurrence of a column value

[ambi95]

Hi All,
My data is coming from DirectQuery source.

I have a calculated column in my data, columnX : it contains duplicates.
I want to count the occurrences of these duplicates in another calculated column - so that I can use it in a slicer later (where I would like to slice by data that does not reccur).

Please can someone help me, as none of the aggregation functions like count, countx, sumx, etc seem to work in DirectQuery.

I am also not able to make changes using PowerQuery.

I am able to create a measure, but since measures can't be used as slicers - the problem isnt getting solved.

Please help out.

[devesh_gupta]

@ambi95 You need to go for dynamic segmentation to use measure as a slicer.

For that you can follow this video: https://youtu.be/CuczXPj0N-k?si=hj_G6L609xEkT-Zi

If it helps, please mark it as an accepted solution so that other users can find it more easily.

[123abc · Accepted solution]

When working with DirectQuery data sources in Power BI, certain operations that require calculations or transformations at the data model level may not be supported because the data is retrieved directly from the source and not stored in a Power BI data model. This can limit the use of DAX calculations and calculated columns.

However, if you want to count the occurrences of duplicates in a column in a DirectQuery scenario and use it in a slicer, you can explore the following approach:

1. SQL Query: If your data source supports it (e.g., SQL Server), you can try to use custom SQL queries to perform the desired calculations directly in your DirectQuery source. You would write SQL code to count the occurrences and then load the result into Power BI.

2. Preprocessing: If possible, consider preprocessing your data source to include a count of occurrences of duplicates as a new field before it reaches Power BI. Depending on your data source, this can be done using SQL views, stored procedures, or other data transformation tools.

3. Incremental Load: Implement an incremental load process if your data source allows it. In an incremental load, you periodically update a separate table in Power BI with aggregated data from your DirectQuery source. This table can store the counts you need, and you can then use it as a slicer.

4. Dataflow and Power Query: If your data source is flexible enough, you can use Power Query to create a dataflow that performs the desired data transformations, including counting duplicates, before loading the data into Power BI. Dataflows can be scheduled to refresh periodically.

5. Parameter Tables: Create a parameter table within your Power BI report that you maintain manually or update via Power Query. This table can store values you want to use for slicing. While this approach doesn't directly count duplicates, it allows you to manually maintain a list of values that you can use for filtering.

6. Custom Visuals: Consider using custom visuals or custom code to achieve the desired functionality. Some custom visuals and extensions in Power BI allow for more advanced calculations and interactions.

In cases where the data source is limited due to DirectQuery constraints, you may need to resort to a combination of these approaches to achieve the desired functionality while working within the constraints of DirectQuery. Always keep in mind the limitations and capabilities of your specific data source and Power BI configuration when designing your solution.