Thanks Ken & MIke for the presentation! Congrats for the initiative always! For sure I will adopt in my Power Pivot spreadsheets the tips I have learned with Mike today!
@josericardo3015, if you need any help learning Power Pivot, check out my Self Service BI Academy. It's an extremely comprehensive program that covers Power Query, Power Pivot, Dimensional Modeling and DAX (in Excel) as well as Power BI: skillwave.training/shop/self-service-bi-academy/
Hi Eric. Great question. To really understand the difference your data model needs to include a hierarchy. If it doesn't include one then the only difference is that MEMBERS includes an ALL value whilst CHILDREN does not. Suppose you create a hierarchy in your data model called Location. It contains a column called Region and a column called Country. You create a CUBESET function that references location.children. This will list only the regions as that is the top level of the hierarchy but location.members will list all the region names and all the country names Does that help? It probably takes more space than I have here to give a detailed explanation
Awesome sessions! Cube formulas can be sooooo powerful when there is an appropriate scenario for it. One question: Will you or Mike provide sample files for this session? Either completed or blank? Thanks again!
Excel-lent explanation and demonstration of the function. Thanks guys!
Very nice presentation. Nice samples, great logic, no overcomplication. Didn't touch cube formulae for an year or so, played a bit with sample 3 to recall the stuff
slicerSelection = LAMBDA( slicer, [ModelConnection],
LET(
connection, IF( ISOMITTED(ModelConnection), "ThisWorkbookDataModel", ModelConnection),
itemsSelected, CUBESETCOUNT( CUBESET(connection, slicer) ),
CUBERANKEDMEMBER( connection,slicer, SEQUENCE( itemsSelected ) )
));
/*
=TEXTJOIN(", ", , slicerSelection(Slicer_CountrySoldTo) )
*/
filteredValue = LAMBDA( measure, table, field, item, [ModelConnection],
LET(
connection, IF( ISOMITTED(ModelConnection), "ThisWorkbookDataModel", ModelConnection),
CUBEVALUE( connection, "[Measures].[" & measure & "]", "[" & table & "].[" & field & "].[" & item & "]" )
) );
/*
=REDUCE(0, slicerSelection(Slicer_CountrySoldTo),
LAMBDA( a, v, a + filteredValue( "Total Revenue", "SalesYTD", "CountrySoldTo", v ) ) )
*/
Thanks Ken & MIke for the presentation!
Congrats for the initiative always!
For sure I will adopt in my Power Pivot spreadsheets the tips I have learned with Mike today!
@josericardo3015, if you need any help learning Power Pivot, check out my Self Service BI Academy. It's an extremely comprehensive program that covers Power Query, Power Pivot, Dimensional Modeling and DAX (in Excel) as well as Power BI: skillwave.training/shop/self-service-bi-academy/
Great presentation!
I am curious what the difference is at time stamp (45 minutes) between .Members & .children ?
Hi Eric. Great question. To really understand the difference your data model needs to include a hierarchy. If it doesn't include one then the only difference is that MEMBERS includes an ALL value whilst CHILDREN does not.
Suppose you create a hierarchy in your data model called Location. It contains a column called Region and a column called Country. You create a CUBESET function that references location.children. This will list only the regions as that is the top level of the hierarchy but location.members will list all the region names and all the country names
Does that help? It probably takes more space than I have here to give a detailed explanation
Awesome sessions! Cube formulas can be sooooo powerful when there is an appropriate scenario for it. One question: Will you or Mike provide sample files for this session? Either completed or blank?
Thanks again!
We will for sure, hopefully later today!
Hi again, the example files are now available for download here: 1drv.ms/u/s!AuhhLgCXSCKKgZEAiDSqRJWzVCrMbA?e=d1vxjC