cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hnguy71 Member
Member

Power Query Find Amount of Iterations + Dynamic Table

Hi All.
This is a two part question, which I hope is an easy fix.
Part 1 is that I need some query expertise. Let's say I have a list with 5000 records. In query, I would like to see how many phases or iterations my list can be broken into divisible by 1000. So in this case, if we have 5000 records we would have 5 phases or iterations. I definitely can do a long conditional column to achieve my desired result but I'm wondering if there's a more dynamic approach. If you want to follow along here's the query I'm currently using:

let
    Source = {1..5000},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "HowManyPhases", each if [Column1] >= 1 and [Column1] <= 1000 then "Phase 1" else if [Column1] >= 1001 and [Column1] <= 2000 then "Phase 2" else if [Column1] >= 2001 and [Column1] <= 3000 then "Phase 3" else if [Column1] >= 3001 and [Column1] <= 4000 then "Phase 4" else if [Column1] >= 4001 and [Column1] <= 5000 then "Phase 5" else "Last")
in
    #"Added Custom"

Part 2 is creating a dynamic table. Using a slicer to determine the phase, I am using SELECTEDVALUE to retrieve it's value. This is the part where I'm stuck. The following formula returns a blank table:

NewTable = 
var mySelection = SELECTEDVALUE(Query1[HowManyPhases])
return
FILTER(SELECTCOLUMNS(Query1, "test", Query1[Column1], "iterations", Query1[HowManyPhases]), [iterations] = mySelection)

However, when I hardcode which phase I want I am able to return the desired table:

NewTable = 
var mySelection = SELECTEDVALUE(Query1[HowManyPhases])
return
FILTER(SELECTCOLUMNS(Query1, "test", Query1[Column1], "iterations", Query1[HowManyPhases]), [iterations] = mySelection)

Any ideas as to why selectedvalue isn't working properly?

4 REPLIES 4
TeigeGao Super Contributor
Super Contributor

Re: Power Query Find Amount of Iterations + Dynamic Table

Hi @hnguy71 ,

For the first problem, we can use the following step:

#"Added Custom" = Table.AddColumn(#"Changed Type", "HowManyPhases", each "Phase " & Text.From(Number.IntegerDivide([Column1],1000)+1))

For the second problem, you are going to create a dynamic table based on the slicer value, it is not possible, the calculated table and calculated column are calculated when you create them, the content in them will not be changed by the filter, as a result, the function selectedvalue is useless to them.

The method you are using is only useful for measure, because the value of measure will be changed by filter outside.

Best Regards,

Teige

hnguy71 Member
Member

Re: Power Query Find Amount of Iterations + Dynamic Table

Hi @TeigeGao 
Thanks for the query solution! It works pretty **bleep** good. 
As for the second issue I still think it's possible to dynamically create a physical table using selected value. My new idea is to have a measure return me the absolute singular value so I can pass that into my table. Here's what I have so far (currently not working):

FindMe = 
var selectMe = SELECTEDVALUE(Query1[HowManyPhases])
var Formula1 = CALCULATE(MAX(Query1[HowManyPhases]), FILTER(ALL(Query1[HowManyPhases]), Query1[HowManyPhases] = selectMe))
var Formula2 = LOOKUPVALUE(Query1[HowManyPhases], Query1[HowManyPhases], SELECTEDVALUE(Query1[HowManyPhases]))
return
Formula2

My current results look like this:
current_result.png

I turned off interations between the two but my desired result would be to return all Phase 1 within my measure.  I know it's possible but I feel like I'm missing something.

TeigeGao Super Contributor
Super Contributor

Re: Power Query Find Amount of Iterations + Dynamic Table

Hi @hnguy71 ,

Actually, it isn't possible to create a dynamic calculated table. However, this doesn't mean that we can't figure a workaround to solve your problem.

Based on my understanding, you want to use the result of a measure to filter a table, (if I select Phase 1, it will only display the data of Phase 1) please correct if any misunderstand. 

This is not hard, drag the HowmanyPhase column to the filter directly:

PBIDesktop_5qmyZR152X.png

Best Regards,

Teige

Highlighted
hnguy71 Member
Member

Re: Power Query Find Amount of Iterations + Dynamic Table

Sorry @TeigeGao ,
I still do want to use the result of a measure to filter a table. If you remove the filtering experience between the two visuals you'll notice that the measure using selectedvalue returns all the phases: 1, 2,3,4, etc. 

 

What I would like the desired measure output to be like this:

MeasureColumn1HowManyPhases
Phase 1700Phase 1
Phase 1800Phase 2
Phase 1900Phase 3
Phase 1100Phase 4
Phase 1560Phase 1
Phase 1500Phase 2
Phase 1780Phase 3

So when I select Phase 1 within my slicer that's my total output. Hope that cleared things up