Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I want to calculate an inflow. What I do is this:
Inflow Inside 3 =
VAR OfficePrevious = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
--,PREVIOUSMONTH('Date'[Date]), ALL('Date')
,'Date'[Month Number] = 4
,DataFte[Internal] = "Y"
,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
VAR OfficeCurrent = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
,'Date'[Month Number] = 5
,DataFte[Internal] = "Y"
,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
VAR ResTable = EXCEPT(OfficePrevious, OfficeCurrent)
RETURN ResTable
As you can see my period selection is set not dynamically, that will come later. But my issue now is that I calculate 2 tables and with Except I create a table with the diff (this is the inflow if I'm correct). But I can't simply return the Restable, so I have to do something with it. But when I use it in a GROUPBY or SUMMARIZE or CALCULATE the error that comes is 'Table variable cannot be used in current context because a base table is expected'.
So is there anybody who can help me to make a distinct count of the EmpId field in the results in the ResTable table which content is (by example):
OfficeCode EmpId
23 4
25 7
My PBIX example file is located here; https://easyupload.io/i1xygt and the measure is called Inflow Inside 3.
Solved! Go to Solution.
Hi @MiKeZZa ,
Measure return a single value, meaning that you cannot make a measure that returns a table, this is the case that you are getting you are creating one table that returns the values.
In your case what you need to do is to count the number of rows for each table and return the result.
The result of your current table is:
The result of your previous table is:
If you llok at the values you have
Office | Current | Previous | Diff |
20 | 1 row | 1 row | 0 |
21 | 1 row | 1 row | 0 |
22 | 1 row | 1 row | 0 |
23 | 0 rows | 1 row | -1 |
24 | 2 rows | 0 rows | 2 |
25 | 0 row | 1 row | -1 |
26 | 25 rows | 0 rows | 2 |
If you redo your measure to the code below you get the correct result:
Inflow Inside 3 =
VAR OfficePrevious = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
--,PREVIOUSMONTH('Date'[Date]), ALL('Date')
,'Date'[Month Number] = 4
,DataFte[Internal] = "Y"
,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
VAR OfficeCurrent = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
,'Date'[Month Number] = 5
,DataFte[Internal] = "Y"
,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
VAR ResTable = COUNTROWS(OfficeCurrent) - COUNTROWS( OfficePrevious)
RETURN ResTable
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MiKeZZa ,
Measure return a single value, meaning that you cannot make a measure that returns a table, this is the case that you are getting you are creating one table that returns the values.
In your case what you need to do is to count the number of rows for each table and return the result.
The result of your current table is:
The result of your previous table is:
If you llok at the values you have
Office | Current | Previous | Diff |
20 | 1 row | 1 row | 0 |
21 | 1 row | 1 row | 0 |
22 | 1 row | 1 row | 0 |
23 | 0 rows | 1 row | -1 |
24 | 2 rows | 0 rows | 2 |
25 | 0 row | 1 row | -1 |
26 | 25 rows | 0 rows | 2 |
If you redo your measure to the code below you get the correct result:
Inflow Inside 3 =
VAR OfficePrevious = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
--,PREVIOUSMONTH('Date'[Date]), ALL('Date')
,'Date'[Month Number] = 4
,DataFte[Internal] = "Y"
,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
VAR OfficeCurrent = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
,'Date'[Month Number] = 5
,DataFte[Internal] = "Y"
,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
VAR ResTable = COUNTROWS(OfficeCurrent) - COUNTROWS( OfficePrevious)
RETURN ResTable
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |