DAX Calculated Column with "variable tables" as variables.
Greetings I'm attempting to create a calculated column that brings out a result based on the rowscount form another table. The relation is ONLY the id of each individual. The following illustrates the relationship between 2 appointment tables, each of a diferente service. Basically, if a person with an appointment on the left table has an apointment on the right table (along some other variables related to dates and service details), the result is either YES or NO, depending on whether the rowcount of a variable, filtered AND related table is more than 0, or not. column = --[state] can adopt values 1 to 5, and there are blank values. var table1 = filter(relatedtable(table2), not(isblank([state]))) var table2 = filter(relatedtable(table2), [state] in {1, 2}) var table = if(countrows(table2) < 1, table1, table2) var conditioned_tableA = filter (table, ## additional conditions related to date and other variables.##) var countB = countrows(conditioned_tableA) var conditioned_tableB = filter (table, ## different conditons from A ##) var countB = countrows(conditioned_tableB) return switch ( true() , countA > 0, "YES" , countB > 0, "NO" , "YES" ) However, I'm getting the error the expression refers to multiple columns. multiple columns cannot be converted to a scalar value Of course, I know that I can't treat a table as a single value, or viceversa. But that is not what I want. The basis table can either be table1 or table2 depending on wether table2 holds any records. But my end result still is just the count of rows. ¿Any ideas?
Previous QTD as of today for each quarter
Help me to create a Dax measure to show the Previous QTD actuals as of day(today) for each quarter. I have date , amount columns for example today is 6/19/2024 so we would like to see 2024-Q1 data from start of quarter to till 3/19/2024 2023-Q4 data from start of quarter to till 12/19/2023 2023-Q3 data from start of quarter to till 9/19/2023 2023-Q2 data from start of quarter to till till 6/19/2023 Currently, I am using this measure. But this is giving Total QTD.Previous Quarter QTD Actuals should be less than QTD Rev Previous Quarter QTD Actuals = calculate(SUM(Amount]),DATEADD(filter(DATESQTD('Calendar'[DateVal]),Calendar'[DateVal]<TODAY()),-1,QUARTER))
DAX query required for IF and IF Else condition of below case
Hi Team, I am new to PBI, help me with this case! IF type(col) = "AAA" & Date time(col) < 11AM then Pickup date = Date(col)-1 ELSE Pickup date = Date(col) IF type(col) = "BBB" then Pickup date = Date(col)–1 IF type(col) = "CCC" then Pickup date = Date(col) Value of "AAA" is having AAA_a, AAA_b, AAA_c, AAA_d, AAA_e ----AAA_n etc. So here we are calling the condition with "AAA". How do we calling "AAA" with one IF condition statement or any other. Same like value of "BBB" & Value of "CCC". Here date time(col) contains 24hrs, we required less than(< )upto 11AM then Pickup date = Date(col)-1, else Pickup date = Date(col). Here Date(col) is calculated column. So, kindly helpe me with the DAX query. below is the snapshot for your reference.
Hep with the formula
Anonymous Anonymous CarlossSainz Hi I have a formula below for calculating the sum of gross risk potential in the table. I want the sum calculation based on the risk name instead of summing up the entire column. I tried the below formula but it is not giving the correct value. I can give an example The total sum of gross risk potential is 100. But the gross risk potential for risk A is 20. I want to get this separate value for all the risks i have in a single column without creating multiple columns. TotalRiskPotentialPerRisk = SUMX( VALUES(Top_Risk_Controls[Riskname]), CALCULATE( SUM(Top_Risk_Controls[Gross_risk_potential]), Top_Risk_Controls[Riskname] = EARLIER(Top_Risk_Controls[Riskname]) ) ) In the above formula, im getting the same values as the gross risk potential column instead of the sum of them based on the risk name. How to get the sum of the values based on the risk name. Please note that the data source is the sharepoint list.Split multiple columns into rows using DAX
Hi Jihwan_Kim & other DAX experts, Thanks Jihwan_Kim for your help earlier. My query is, The condition to split is there are multiple columns to be splitting into rows. I have done splitting from 'Raw Data' table into rows in Table A for column 'List of Students', as below image and I would like to do as per in Table A+ (add new calculated column for the Activity) where it has multiple values. E.g. John has multiple activities. Really appreciate if anyone can help me to achieve from Table A to Table A+. Note: I have done using Power Query before, but due to performance issue I have to move to DAX instead Thank youNeed help to Get latest records from table data by adding flag
Need to get latest record based on each file group on below conditions. PBIX URL : https://drive.google.com/file/d/1YNkpQQKhtOejgPtj48bmS6v6MyadYnfN/view?usp=sharing 1.Sourcesystem="DB" is latest. 2. if file is not available in Sourcesystem="DB" ,check Sourcesystem="App" and Sourcesystem="app" and stepdescription = "Import " is latest. 3. IF stepdescription = "Import " also file not available. stepdescription = "Transfer " is latest. Pls find below screen shot for source data and highlighted color indicates expected output. Tried below script but expected result not coming . need help. Test 1 = IF ( CALCULATE( COUNTROWS( VALUES('Data'[FileName]) ), FILTER( ALL('Data'), 'Data'[FileName] = EARLIER('Data'[FileName]) && 'Data'[SourceSystem] = "DB" ) ) > 0, IF ( 'Data'[SourceSystem] = "DB" , 1, 0 ), IF ( 'Data'[SourceSystem] = "App" && ( CALCULATE( MAX('Data'[StepDescription]), FILTER( ALL('Data'), 'Data'[FileName] = EARLIER('Data'[FileName]) && 'Data'[SourceSystem] = "App" && 'Data'[StepDescription] = "import" ) ) = 'Data'[StepDescription] || CALCULATE( MAX('Data'[StepDescription]), FILTER( ALL('Data'), 'Data'[FileName] = EARLIER('Data'[FileName]) && 'Data'[SourceSystem] = "App" && 'Data'[StepDescription] = "transfer" ) ) = 'Data'[StepDescription] ), 1, 0 ) ) above script giving output like below .Printing content of web link in power bi table [ column] in one go
I have use case , A table has column mnamed LINK. it hold https link to a pdf content. there will be more rows in table with link as column. I am trying find solution to have button [ one click] does below. 1. Iterates all the links in LINK column 2. hit the link and get the PDF content one by one 3. Send the pdf to printer. How can i achieve this ?
100% stacked bar chart - sort of text column by index with same colors in legend
Hi, I want to create a 100 % stacked bar chart which has data like below Ticket Created Time spent state Index id 1 10/24/2023 16:49 1 P4 1 id 1 10/24/2023 17:23 0 P4 1 id 1 10/24/2023 17:40 1 P2 2 id 1 10/24/2023 18:02 0 P2 2 id 1 10/24/2023 18:07 0 P2 2 id 1 10/24/2023 18:10 0 P2 2 id 1 10/24/2023 18:38 0 P2 2 id 1 10/24/2023 18:49 0 P3 3 id 1 10/24/2023 18:50 13 P3 3 id 1 10/25/2023 7:13 0 P3 3 id 1 10/25/2023 7:18 0 P3 3 id 1 10/25/2023 7:21 1 P3 3 id 1 10/25/2023 8:20 0 P3 3 id 2 10/24/2023 16:49 1 P4 1 id 2 10/24/2023 17:23 0 P4 1 id 2 10/24/2023 17:40 1 P2 2 id 2 10/24/2023 18:02 0 P2 2 id 2 10/24/2023 18:07 0 P2 2 id 2 10/24/2023 18:10 0 P2 2 id 2 10/24/2023 18:38 0 P2 2 id 2 10/24/2023 18:49 0 P3 3 id 2 10/24/2023 18:50 13 P3 3 id 2 10/25/2023 7:13 0 P3 3 id 2 10/25/2023 7:18 0 P3 3 id 2 10/25/2023 7:21 1 P3 3 id 2 10/25/2023 8:20 0 P3 3 in y-axis=Ticket, x-axis=Time spent, legend=Index, but this legend does not give the same colors for all state in a id. Also, tried sorting column state using Sort by column index which gives an error such as"there can't be more than one value in index for the same state". Would really be helpfull if there's a way to sort state by index and it should have same colors for all ticket id. Please Note: The legend has to be sorted by index+it should same colors for every state in each id as well well over all other id's. Thanks for your help in advanceDax Week Calculation - Power BI
Hey folks! I'm having some trouble trying to calculate weeknumbers.... That's because, i have a sells time period, and i want to check how many weeks are left of that period. I've tried DATEDIFF function, and none got the result i was expecting... I'll give an example based on one product that i have, but i noticed that it applies for the rest as well. My first date is 26/10/2022, and last is 05/06/2023, there's 222 days in that period, which returns me 31.71428 weeks. As there isn't a 1.5 week, those decimal numbers results on 31 full weeks, and 5 days. Here's the dax formula i'm using: And here's my results: The point is, i do not have 32 weeks, but the formula is rounding and returning me this unexisted additional weeknumber. I'm expecting that from day 26/10/2022 to 01/11/2022 is week 31, 02/11/2022 to 08/11/2022 is week 30 and so on. If i use a rounddown function, that's the result that i get: Still not giving me the right week number, because it is expected that day 01/11/2022 is week num 31. I'm cracking my head out to figure it out a way to solve this, and i noticed one thing.... When i get DATEDIFF on days, and then divide by 7 (to bring weeknumbers), that's what i'm getting : My decimals results in a pattern that shows me exactly when weeknum should change (except for my first value). For example, the first result is "31.71429" and when day is 01/11/2022, result is "30.85714". But in day 02/11/2022, where i'm expecting weeknum 30, my result is "30.71429" again.... same 0.71429 decimal number. Maybe that's the key to solve this problem (still trying over here), but to round down every decimal under or equal 0.71429, EXCEPT for my largest value, because in 31.71249 i'm expecting 31 and not 32 value on weeknumber.
