dynamic filtering

3 Topics

LINESTX - not able to see the intercept and slope values
Hello, I have started learning how to use the LINESTX fx and Dynamic filtering. I have a small table with 'Index', 'Gender', Event', 'Y' ( 'dependent variable' ) and 'x1' - 'x4' (independent variables). At the bottom I have added a snippet of the table I am working with. I would like that when using the event and/or gender slicers, the correct values (i.e slope and intercept) is calculated. The measure I am using is: PREDICTION = VAR line = LINESTX ( ALLSELECTED ('LinearRegression'[X1]), --to keep slicers [Total_Y], --Expression for Y axis 'LinearRegression'[X1] ---Expression for X axis ) VAR slope = SELECTCOLUMNS(line, [Slope1]) VAR intercept = SELECTCOLUMNS(line, [Intercept]) VAR x = SELECTEDVALUE('LinearRegression'[X1]) VAR y = x * slope + intercept RETURN y This measure seems to give me a dynamic value so I am assuming is working, however, I would like to see the value calculated for Slope1 and Intercept, and here is where my issue is. When I 'isolate' the measure that is supposed to get me these values it just doesn't work. The 'isolated'measure I mean is : SlopeIntercept = LINESTX ( ALLSELECTED ('LinearRegression'[X1]), [Total_Y], 'LinearRegression'[X1] ) The error message I keep getting is The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. Same happens when I try to use the LINEST fx (LINEST ([column Y], [column x]) and use the column 'y' instead of the measure [Total_Y]. The data types in Power BI seem all to be correct. I am definitely missing something. Any help/feedback with this is much appreciated. Index Gender Event Y X1 X2 X3 X4 0 Female A 118.32 28.05 58.01 29.96 88.45 1 Female B 119.34 28.28 58.45 30.17 89.18 2 Female C 120.17 28.51 59.06 30.55 89.77 3 Female D 120.91 28.35 58.95 30.6 90.02 4 Female E 124.69 29.26 60.56 31.3 92.81 5 Female A 57.43 27.14 30.29 6 Female B 57.44 26.48 30.96 7 Male C 60.99 28.59 32.4 8 Male D 61.15 29.2 31.95 9 Female E 61.11 28.29 32.82 10 Female A 61.67 30.12 31.55 11 Female B 59.86 29.12 30.74 12 Male C 121.92 27.51 59.2 31.69 90.98 13 Male D 117.35 26.07 55.65 29.58 85.87 14 Female E 139.91 29.64 65.01 35.37 106.91 15 Female A 137 30.4 65.21 34.81 104.42 16 Female B 134.52 30.08 65.23 35.15 102.99 17 Female C 134.45 28.98 64.03 35.05 103.35
Solved
Anonymous
1 year ago Place DAX Commands and Tips
2.3KViews
0likes
3Comments
Exporting a Table from PowerBI Service with Dynamic Filters
Hello PBI Community, I have been attempting to create a PowerAutomate flow within my PowerBI report to allow users to select a button to export the table on the report into a SharePoint folder. I have been facing some difficulty with this flow so I was hoping this community could help solve it. For context, I have a published PowerBI report with a normal table visual on it. I am attempting to add the PowerAutomate button to the page with a linked flow that, upon clicking, triggers the export of the current filtered table, with page slicers applied, to a SharePoint folder. The two filters/slicers that are key for the filtering and dynamic exporting are "Asset" and "Work Order Number". I've tried a few different methods, with some success on getting the table exported, but not filtered down to the "sliced" view. Has anyone dealt with something like this before?
Dawson16
3 years ago Place DAX Commands and Tips
475Views
0likes
1Comment
Dynamic slicers for time intelligence not working as expected
I have a golden dataset that serves as the basis for many reports and for adhoc data exploration. It has a star schema with one FACT table. My problem is with creating dynamic date slicers for time intelligence. The FACT table has data for month-end dates and current MTD weekday dates only, that is not continuous dates. All measures are in a measures table, FACT table hidden from dataset users. We have a designated date table with continuous dates from 2015 through 2024, which more than the time frame of the FACT table. Using DAX, we created a Relative Time table that has business relevant filters defined, such as Current Date (not sysdate but latest date from FACT table), Last Audited Month (again per FACT table filters), etc. This table allows for dynamic slicers on report pages. Recently we were asked to add time intelligence calculations using calculation groups to this dataset, which was not a problem. We created items like: Selected Month, Prior Month, MoM, MoM%, Selected QTD, Prior QTD, QoQ, QoQ%, etc. See below picture. The definitions are based on the date table as it has continuous dates. This all works as long as the filter on the report is based on the date table. However, it does not work with the nested Relative Time, 'Date Table' As of date combined filter's dynamic filtering. The problem is that if a Relative Time is selected from the filter, the filtering does not work. Shows the proper calculations for every date in the FACT table, no filtering. If a date is selected then it filters for that date but then the slicer is not dynamic, following month has to be reset. Our requirement is to have the time intelligence calculations work with the dynamic filters. Here is the sample DAX from the time intelligence calculation group: CALCULATIONGROUP '0 Relative Time Calculations'[Time Measures] CALCULATIONITEM "Selected Month" = CALCULATE( SELECTEDMEASURE() ,CROSSFILTER( '0 Date Table'[As Of Date], '0 Relative Time'[ASOF_DT], BOTH ) ) Ordinal = 0 CALCULATIONITEM "Prior Month" = CALCULATE( SELECTEDMEASURE() , PREVIOUSMONTH('0 Date Table'[As Of Date]) , CROSSFILTER( '0 Date Table'[As Of Date], '0 Relative Time'[ASOF_DT], BOTH ) ) Ordinal = 1
AdrienneB
3 years ago Place DAX Commands and Tips
646Views
0likes
1Comment